﻿<%@ WebHandler Language="C#" Class="YPContainerStepCheckAndShipReport" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using System.IO;
using ReportBusiness;

/**
 * Description: 叶片零件流转信息查询报表
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.2.28
 */

public class YPContainerStepCheckAndShipReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询公用sql
    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT cci.mfgordername,cci.familylistnames,cci.productname,cci.productdescription,cci.containername,cci.qty,
       cci.workflowstepname currentworkflowstepname,
       s.specno,
       s.opdesc,
       csd.checkdate,
       ff.factoryname,
       w.workcentername,
       ship.status,
       ship.senddate,
       ship.sender,
       ship.needcompletedate,
       ship.toacceptdate,
       ship.toacceptor,
       ship.estimatedcompletedate,
       ship.completedate,
       ship.recivedate,
       ship.returnemp,
       ns.specno nextspecno,
       ns.opdesc nextopdesc,
       nf.factoryname nextfactoryname,
       nw.workcentername nextworkcentername,
       ncsd.checkdate nextcheckdate
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN factory ff ON ff.factoryid = s.factoryid
LEFT JOIN (
  SELECT csd.containerid,csd.containername,csd.workflowstepname,csd.factoryid,csd.workcenterid,csd.checkdate,
         row_number() OVER(PARTITION BY csd.containerid,csd.containername,csd.workflowstepname,csd.factoryid,csd.workcenterid ORDER BY csd.checkdate DESC) seq 
  FROM containersteprecord csd
  LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
  WHERE cci.isinstore = 0 AND cci.originalfactoryid = '{0}'";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND csd.containername = '{0}'", jObject["containername"].ToString());
        }
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND csd.productname = '{0}'", jObject["productname"].ToString());
        }
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }
        sql += @"
) csd ON csd.seq = 1 AND csd.containerid = cci.containerid AND csd.workflowstepname = ws.workflowstepname
LEFT JOIN workcenter w ON w.workcenterid = csd.workcenterid
LEFT JOIN workflowstep nws ON nws.workflowid = cci.workflowid AND nws.sequence = ws.sequence + 1
LEFT JOIN specbase nsb ON nsb.specbaseid = nws.specbaseid
LEFT JOIN spec ns ON ns.specid = NVL(nsb.revofrcdid,nws.specid)
LEFT JOIN (
  SELECT csd.containerid,csd.containername,csd.workflowstepname,csd.factoryid,csd.workcenterid,csd.checkdate,
         row_number() OVER(PARTITION BY csd.containerid,csd.containername,csd.workflowstepname,csd.factoryid,csd.workcenterid ORDER BY csd.checkdate DESC) seq 
  FROM containersteprecord csd
  LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
  WHERE cci.isinstore = 0 AND cci.originalfactoryid = '{0}'";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND csd.containername = '{0}'", jObject["containername"].ToString());
        }
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND csd.productname = '{0}'", jObject["productname"].ToString());
        }
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }
        sql += @"
) ncsd ON ncsd.seq = 1 AND ncsd.containerid = cci.containerid AND ncsd.workflowstepname = nws.workflowstepname
LEFT JOIN factory nf ON nf.factoryid = ncsd.factoryid
LEFT JOIN workcenter nw ON nw.workcenterid = ncsd.workcenterid
LEFT JOIN (
  SELECT cr.containershiprecordid,cci.containername,ws.workflowstepname,cr.senddate,e.fullname sender,cr.needcompletedate,e2.fullname toacceptor,cr.toacceptdate,
         cr.estimatedcompletedate,csr.completedate,csr.recivedate,e3.fullname returnemp,
         CASE WHEN csr.recivedate IS NOT NULL THEN '已转入'
              WHEN csr.completedate IS NOT NULL THEN '已完成'
              WHEN cr.toacceptdate IS NOT NULL THEN '已接收'
              WHEN cr.senddate IS NOT NULL THEN '已转出'
              ELSE '待转出' 
         END status
  FROM containershiprecord cr
  LEFT JOIN containershiprecive csr ON csr.containershiprecordid = cr.containershiprecordid AND csr.isuse = 0
  LEFT JOIN containershiprecordstep crdd ON crdd.containershiprecordid = cr.containershiprecordid
  LEFT JOIN workflowstep ws ON ws.workflowstepid = crdd.workflowstepid
  LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
  LEFT JOIN employee e ON e.employeeid = cr.sender
  LEFT JOIN employee e2 ON e2.employeeid = cr.toacceptor
  LEFT JOIN employee e3 ON e3.employeeid = csr.reciver
  WHERE cr.isuse = 0 AND cr.factoryid = '{0}'";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }
        sql += @"
) ship ON ship.containername = cci.containername AND ws.workflowstepname = ship.workflowstepname
WHERE cci.isinstore = 0 AND cci.originalfactoryid = '{0}' AND cci.workflowroute IS NULL";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }
        sql = string.Format(sql, jObject["factoryid"].ToString());
        sql += " ORDER BY cci.containername,s.specno";
        return sql;
    }

    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["familylistnames"].Caption = "型别";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["currentworkflowstepname"].Caption = "当前工序";
        dtResult.Columns["specno"].Caption = "工序号";
        dtResult.Columns["opdesc"].Caption = "工序名称";
        dtResult.Columns["checkdate"].Caption = "工序完工时间";
        dtResult.Columns["factoryname"].Caption = "工序主制分厂";
        dtResult.Columns["workcentername"].Caption = "工序主制工区";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["senddate"].Caption = "转出时间";
        dtResult.Columns["sender"].Caption = "转出人";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["toacceptor"].Caption = "接收人";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["returnemp"].Caption = "转入人";
        dtResult.Columns["nextspecno"].Caption = "下工序号";
        dtResult.Columns["nextopdesc"].Caption = "下工序名称";
        dtResult.Columns["nextfactoryname"].Caption = "下工序主制分厂";
        dtResult.Columns["nextworkcentername"].Caption = "下工序主制工区";
        dtResult.Columns["nextcheckdate"].Caption = "下工序完工时间";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "零件流转信息查询报表");
    }
}